using System;
using CodeAngel.Domain.Framework;
using Smo = Microsoft.SqlServer.Management.Smo;
using System.Data.SqlClient;
using System.Diagnostics;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;

namespace CodeAngel.Domain
{
    public partial class Database
    {
        #region SetDefaultPropertyValues

        /// <summary>
        /// Sets the properties of a new entity to its default values.
        /// </summary>
        internal override void SetDefaultPropertyValues()
        {
            this.SetFriendlyIdAsync();
            this.DatabaseID = Guid.NewGuid();
        }

        #endregion

        #region CreateSqlServerDatabase

        /// <summary>
        /// Creates the SQL Server database based on the meta-data described in this Database entity.
        /// </summary>
        public void CreateSqlServerDatabase()
        {
            // Verify that we have valid database and server names.
            if (this.Name.Trim().Length < 1)
                throw new Exception("Must have database name to create a database.");
            if (this.Server.Trim().Length < 1)
                throw new Exception("Must have server name to create a database.");

            // Get access to the server and verify that the database does not already exist.
            var ThisServer = this.GetServer(this.Server);

            if (ThisServer.Databases.Contains(this.Name))
                throw new Exception("The '" + this.Name + "' database already exists on '" + this.Server + "' server.");

            // Create and return the new SQL Server database.
            Smo.Database ThisDatabase = new Smo.Database(ThisServer, this.Name);
            ThisDatabase.Create();
            ThisDatabase.Alter();

            // Now that the database is created, we can add the various objects (e.g., tables, stored procedures, etc.).
            this.CreateDatabaseObjects();
        }

        #endregion

        #region UpdateSqlServerDatabase

        /// <summary>
        /// Updates the SQL Server database based on the meta-data described in this Database entity.
        /// </summary>
        public void UpdateSqlServerDatabase()
        {
            this.CreateDatabaseObjects();
        }

        #endregion

        #region CreateDatabaseObjects

        /// <summary>
        /// To create the objects (tables, stored procedures, default users, permssions, etc.) for 
        /// this Database entity.
        /// </summary>
        private void CreateDatabaseObjects()
        {
            var ThisLogin = this.CreateLogin(this.UserName, this.Password);
            this.CreateUser(this.UserName, ThisLogin);

            this.CreateFriendlyIDTable();
            this.AddFriendlyIdRows();
            this.CreateGetNextFriendlyIdStoredProcedure();
            this.CreateGetUserIDFunction();

            this.CreateTables();
            this.CreateCanExecuteFunction();

            //this.CreateTables();
            this.CreateGetStoredProcedures();
            this.CreateDeleteStoredProcedures();
            this.CreateInsertStoredPrcoedures();
            this.CreateUpdateStoredProcedures();

            this.CreatePermissions();
            this.CreateAdminRoleAndUser();
            this.CreateSecurityForeignKeys();
        }

        #endregion

        #region SqlDatabase

        /// <summary>
        /// To identify the SQL Server database object for this database, if there is one. If there isn't, 
        /// then an exception is thrown.
        /// </summary>
        private Smo.Database SqlDatabase
        {
            get
            {
                if (this._sqlDatabase == null)
                {
                    var ThisServer = this.GetServer(this.Server);
                    if (ThisServer.Databases.Contains(this.Name))
                        this._sqlDatabase = ThisServer.Databases[this.Name];
                    else
                        throw new Exception("Could not find the '" + this.Name + "' database on '" + this.Server + "' server.");
                }
                return this._sqlDatabase;
            }
        }
        private Smo.Database _sqlDatabase;

        #endregion

        #region Domain

        public Domain Domain
        {
            get
            {
                return this.ParentEntity as Domain;
            }
        }

        #endregion

        #region Security

        /// <summary>
        /// To create the GetUserID function, which returns the UserID for the supplied
        /// user name and password.
        /// </summary>
        private void CreateGetUserIDFunction()
        {
            Smo.UserDefinedFunction ThisFunction = new Smo.UserDefinedFunction(this.SqlDatabase, "GetUserID");
            ThisFunction.TextMode = false;
            ThisFunction.FunctionType = Smo.UserDefinedFunctionType.Scalar;
            ThisFunction.DataType = Smo.DataType.UniqueIdentifier;

            //Add the parameters.
            ThisFunction.Parameters.Add(new Smo.UserDefinedFunctionParameter(ThisFunction, "@UserName", this.GetUserNameDataType()));
            ThisFunction.Parameters.Add(new Smo.UserDefinedFunctionParameter(ThisFunction, "@Password", this.GetPasswordDataType()));

            //Create the body of the function.
            var Body = new SqlStringBuilder();
            Body.AddLine("BEGIN");
            Body.AddLine("\tDECLARE @UserID uniqueidentifier");
            Body.AddLine("\tSELECT @UserID = UserID ");
            Body.AddLine("\tFROM [User]");
            Body.AddLine("\tWHERE UserName = @UserName AND Password = @Password");
            Body.AddBlankLine();
            Body.AddLine("\tRETURN @UserID");
            Body.AddLine("END");

            ThisFunction.TextBody = Body.ToString();
            if (this.SqlDatabase.UserDefinedFunctions.Contains(ThisFunction.Name))
                this.SqlDatabase.UserDefinedFunctions[ThisFunction.Name].Drop();
            ThisFunction.Create();

        }

        /// <summary>
        /// To create the CanExecute function, which validates that a user can perform a 
        /// specified operation. If this function already exists, it will be overwritten.
        /// </summary>
        private void CreateCanExecuteFunction()
        {
            Smo.UserDefinedFunction ThisFunction = new Smo.UserDefinedFunction(this.SqlDatabase, "CanExecute");
            ThisFunction.TextMode = false;
            ThisFunction.FunctionType = Smo.UserDefinedFunctionType.Scalar;
            ThisFunction.DataType = Smo.DataType.Bit;

            //Add the parameters.
            ThisFunction.Parameters.Add(new Smo.UserDefinedFunctionParameter(ThisFunction, "@ThisUserID", Smo.DataType.UniqueIdentifier));
            ThisFunction.Parameters.Add(new Smo.UserDefinedFunctionParameter(ThisFunction, "@ObjectName", Smo.DataType.VarChar(50)));
            ThisFunction.Parameters.Add(new Smo.UserDefinedFunctionParameter(ThisFunction, "@OperationType", Smo.DataType.VarChar(50)));

            //Create the body of the function.
            var Body = new SqlStringBuilder();
            var _with2 = Body;
            _with2.AddLine("BEGIN");
            _with2.AddLine("Declare @Count int");
            _with2.AddBlankLine();

            //If the user is in the Admin role, they have permission to do anything.
            _with2.AddCommentBlock("CHECK FOR MEMBERSHIP IN ADMIN ROLE - SUCH MEMBERS ALWAYS HAVE PERMISSION");
            _with2.AddLine("SELECT @Count = Count(*)");
            _with2.AddLine("FROM dbo.User_Role INNER JOIN");
            _with2.AddLine("     dbo.Role ON dbo.User_Role.RoleID = dbo.Role.RoleID");
            _with2.AddLine("WHERE dbo.User_Role.UserID = @ThisUserID AND dbo.Role.Name = 'Admin'");
            _with2.AddLine("If @Count > 0 Return 1");
            _with2.AddBlankLine();

            //Check to see if this user has beed explicitly denied access.
            _with2.AddCommentBlock("CHECK FOR EXPLICITLY DENIED PERMISSION");
            _with2.AddLine("SELECT @Count = Count(*)");
            _with2.AddLine("FROM dbo.User_DeniedPermission INNER JOIN");
            _with2.AddLine("     dbo.Permission ON dbo.User_DeniedPermission.DeniedPermissionID = dbo.Permission.PermissionID");
            _with2.AddLine("WHERE dbo.User_DeniedPermission.UserID = @ThisUserID AND dbo.Permission.ObjectName = @ObjectName AND dbo.Permission.OperationType = @OperationType");
            _with2.AddLine("If @Count > 0 Return 0");
            _with2.AddBlankLine();

            //Check to see if this user has been explicitly granted access.
            _with2.AddCommentBlock("CHECK FOR EXPLICITLY GRANTED PERMISSION");
            _with2.AddLine("SELECT @Count = COUNT(*) ");
            _with2.AddLine("FROM dbo.User_Permission INNER JOIN");
            _with2.AddLine("     dbo.Permission ON dbo.User_Permission.PermissionID = dbo.Permission.PermissionID");
            _with2.AddLine("WHERE dbo.User_Permission.UserID = @ThisUserID AND dbo.Permission.ObjectName = @ObjectName AND dbo.Permission.OperationType = @OperationType");
            _with2.AddLine("If @Count > 0 Return 1");
            _with2.AddBlankLine();

            //Check to see if this user has membership to a role that has been granted access.
            _with2.AddCommentBlock("CHECK FOR INHERITED PERMISSION FROM ROLE MEMBERSHIP");
            _with2.AddLine("SELECT @Count = COUNT(*) ");
            _with2.AddLine("FROM dbo.User_Role INNER JOIN");
            _with2.AddLine("     dbo.Role ON dbo.User_Role.RoleID = dbo.Role.RoleID INNER JOIN");
            _with2.AddLine("     dbo.Role_Permission ON dbo.Role.RoleID = dbo.Role_Permission.RoleID INNER JOIN");
            _with2.AddLine("     dbo.Permission ON dbo.Role_Permission.PermissionID = dbo.Permission.PermissionID");
            _with2.AddLine("WHERE dbo.User_Role.UserID = @ThisUserID AND dbo.Permission.ObjectName = @ObjectName AND dbo.Permission.OperationType = @OperationType");
            _with2.AddLine("If @Count > 0 Return 1");
            _with2.AddBlankLine();

            _with2.AddLine("Return 0");
            _with2.AddLine("END");


            ThisFunction.TextBody = Body.ToString();
            if (this.SqlDatabase.UserDefinedFunctions.Contains(ThisFunction.Name))
                this.SqlDatabase.UserDefinedFunctions[ThisFunction.Name].Drop();
            ThisFunction.Create();
        }

        /// <summary>
        /// Grants the user of this database execute permission for the identified stored 
        /// procedure or function.
        /// </summary>
        private void GrantExecutePermission(Smo.StoredProcedure thisSP)
        {
            Smo.ObjectPermissionSet Permissions = new Smo.ObjectPermissionSet();
            Permissions.Execute = true;
            thisSP.Grant(Permissions, this.UserName);
        }
        private void GrantExecutePermission(Smo.UserDefinedFunction thisFunction)
        {
            Smo.ObjectPermissionSet Permissions = new Smo.ObjectPermissionSet();
            Permissions.Execute = true;
            thisFunction.Grant(Permissions, this.UserName);
        }

        /// <summary>
        /// To create a login on the server of the current database.  If this login already exists, 
        /// it will not be recreated - but the password will be updated to use the identified password.
        /// </summary>
        private Smo.Login CreateLogin(string userName, string password)
        {
            var SqlServer = this.GetServer(this.Server);
            Smo.Login ThisLogin;
            if (SqlServer.Logins.Contains(userName))
            {
                ThisLogin = SqlServer.Logins[userName];
                ThisLogin.ChangePassword(password);
                ThisLogin.DefaultDatabase = this.Name;
                ThisLogin.Alter();
            }
            else
            {
                ThisLogin = new Smo.Login(SqlServer, userName);
                ThisLogin.LoginType = Smo.LoginType.SqlLogin;
                ThisLogin.DefaultDatabase = this.Name;
                ThisLogin.Create(password, Smo.LoginCreateOptions.None);
            }

            return ThisLogin;
        }

        /// <summary>
        /// To create a user for the current database for the identified login.  If the user
        /// already exists, it will not be recreated.
        /// </summary>
        private Smo.User CreateUser(string userName, Smo.Login thisLogin)
        {
            Smo.User ThisUser = default(Smo.User);
            if (this.SqlDatabase.Users.Contains(userName))
            {
                ThisUser = this.SqlDatabase.Users[userName];
                ThisUser.Login = thisLogin.Name;
            }
            else
            {
                ThisUser = new Smo.User(this.SqlDatabase, userName);
                ThisUser.Login = thisLogin.Name;
                ThisUser.Create();
            }
            return ThisUser;
        }

        /// <summary>
        /// To create the permission records (create, read, update, delete) for the identified
        /// entity Def or each entity Def in the current solution.
        /// </summary>
        private void CreatePermissions()
        {
            SqlConnection ThisConnection = this.GetOpenConnection();
            foreach (var ThisED in this.Domain.EntityDefs)
            {
                this.CreatePermissions(ThisED, ThisConnection);
            }
            ThisConnection.Close();
        }

        private void CreatePermissions(EntityDef thisED, SqlConnection thisConnection = null)
        {
            bool CreatedConnection = false;
            if ((thisConnection == null))
            {
                thisConnection = this.GetOpenConnection();
                CreatedConnection = true;
            }

            this.ExecuteTextCommand(GetInsertOrUpdatePermissionStatement(thisED, "Create"), thisConnection);
            this.ExecuteTextCommand(GetInsertOrUpdatePermissionStatement(thisED, "Read"), thisConnection);
            this.ExecuteTextCommand(GetInsertOrUpdatePermissionStatement(thisED, "Update"), thisConnection);
            this.ExecuteTextCommand(GetInsertOrUpdatePermissionStatement(thisED, "Delete"), thisConnection);

            if (CreatedConnection) thisConnection.Close();
        }

        private string GetInsertOrUpdatePermissionStatement(EntityDef thisED, string operationType)
        {
            string Description = "Controls whether a user may " + operationType + " a(n) " + thisED.FriendlyName;
            Guid PermissionID = Guid.NewGuid();
            //string FriendlyID = FriendlyIdService.GetNextFriendlyID("Permission").ToString();
            string FriendlyID = DomainDataServiceClient.Instance.GetNextFriendlyID("Permission").ToString();
            return GetInsertOrUpdatePermissionStatement(PermissionID, FriendlyID, operationType + thisED.Name, Description, operationType, thisED.Name, "Entity");
        }

        private string GetInsertOrUpdatePermissionStatement(Guid permissionID, string friendlyID, string name, string description, string operationType, string objectName, string objectType)
        {
            string PermissionIDString = "'{" + permissionID.ToString() + "}'";
            string FriendlyIDString = "'" + friendlyID + "'";
            name = "'" + name + "'";
            description = "'" + description + "'";
            operationType = "'" + operationType + "'";
            objectName = "'" + objectName + "'";
            objectType = "'" + objectType + "'";

            string Condition = "(SELECT Count(*) FROM [Permission] WHERE Name = " + name + ") = 0 ";
            string Insert = "INSERT INTO [Permission] (PermissionID, FriendlyID, Name, Description, OperationType, ObjectName, ObjectType) " + "VALUES (" + PermissionIDString + ", " + FriendlyIDString + ", " + name + ", " + description + ", " + operationType + ", " + objectName + ", " + objectType + ") ";
            string Update = "UPDATE [Permission] SET FriendlyID = " + FriendlyIDString + ", Name = " + name + ", Description = " + description + ", OperationType = " + operationType + ", ObjectName = " + objectName + ", ObjectType = " + objectType + "WHERE PermissionID = " + PermissionIDString;

            return "IF " + Condition + Insert + " ELSE " + Update;
        }

        /// <summary>
        /// To remove the permissions for the identified entity.
        /// </summary>
        private void DeletePermissions(string entityName, SqlConnection thisConnection = null)
        {
            bool CreatedConnection = false;
            if ((thisConnection == null))
            {
                thisConnection = this.GetOpenConnection();
                CreatedConnection = true;
            }

            try
            {
                this.ExecuteTextCommand("DELETE FROM Permission WHERE ObjectName = '" + entityName + "'", thisConnection);

            }
            catch (SqlException ex)
            {
                //TODO: Dont do this.  Check first if the permission already exists...
                Debug.WriteLine(ex.Message);
            }

            if (CreatedConnection) thisConnection.Close();
        }

        /// <summary>
        /// To remove the permissions for the identified entity.
        /// </summary>
        private void DeleteDomainPermission(Guid permissionID, SqlConnection thisConnection = null)
        {
            bool CreatedConnection = false;
            if ((thisConnection == null))
            {
                thisConnection = this.GetOpenConnection();
                CreatedConnection = true;
            }

            try
            {
                this.ExecuteTextCommand("DELETE FROM Permission WHERE PermissionID = '{" + permissionID.ToString() + "}'", thisConnection);

            }
            catch (SqlException ex)
            {
                //TODO: Dont do this.  Check first if the permission already exists...
                Debug.WriteLine(ex.Message);
            }

            if (CreatedConnection) thisConnection.Close();
        }

        /// <summary>
        /// To insert an admin record into the Role table and User table, and to assign
        /// the User record to the admin role.
        /// </summary>
        private void CreateAdminRoleAndUser()
        {
            Guid RoleID = Guid.NewGuid();
            Guid UserID = Guid.NewGuid();

            string RoleIdString = "'{" + RoleID.ToString() + "}'";
            string UserIdString = "'{" + UserID.ToString() + "}'";
            string User_RoleIDString = "'{" + Guid.NewGuid().ToString() + "}'";
            string UserSettingIDString = "'{" + Guid.NewGuid().ToString() + "}'";
            string Name = "'Admin'";
            string Password = "'df#@0k0j99j$!bbvv%^b'";
            string Description = "'The adminstrator role of this application.  Users in this role may do anything (except for explicitly denied permissions).'";

            string InsertRole = "IF (SELECT Count(*) FROM Role WHERE Name = " + Name + ") = 0 INSERT INTO Role (RoleID, FriendlyID, Name, Description) VALUES (" + RoleIdString + ", 0, " + Name + ", " + Description + ")";
            string InsertUser = "IF (SELECT Count(*) FROM [User] WHERE UserName = " + Name + ") = 0 INSERT INTO [User] (UserID, FriendlyID, UserName, Password) VALUES (" + UserIdString + ", 0, " + Name + ", " + Password + ")";
            string InsertUser_Role = "IF (SELECT Count(*) FROM User_Role INNER JOIN [USER] ON User_Role.UserID = [User].UserID WHERE [User].UserName = " + Name + ") = 0 INSERT INTO User_Role (User_RoleID, UserID, RoleID) VALUES (" + User_RoleIDString + ", " + UserIdString + ", " + RoleIdString + ")";

            var ThisConnection = this.GetOpenConnection();
            this.ExecuteTextCommand(InsertRole, ThisConnection);
            this.ExecuteTextCommand(InsertUser, ThisConnection);
            this.ExecuteTextCommand(InsertUser_Role, ThisConnection);
            ThisConnection.Close();

        }

        /// <summary>
        /// Creates the foreign key relationships between the security tables (User, Role, 
        /// Permission, User_Role, User_Permission, User_DeniedPermission, Role_Permission).
        /// </summary>
        private void CreateSecurityForeignKeys()
        {
            Smo.Table User_RoleTable = this.GetTable("User_Role", TableNotFoundOptionEnum.ReturnNothing);
            this.CreateForeignKey(User_RoleTable, "UserID", "User", "UserID");
            this.CreateForeignKey(User_RoleTable, "RoleID", "Role", "RoleID");

            Smo.Table User_PermissionTable = this.GetTable("User_Permission", TableNotFoundOptionEnum.ReturnNothing);
            this.CreateForeignKey(User_PermissionTable, "UserID", "User", "UserID");
            this.CreateForeignKey(User_PermissionTable, "PermissionID", "Permission", "PermissionID");

            Smo.Table User_DeniedPermissionTable = this.GetTable("User_DeniedPermission", TableNotFoundOptionEnum.ReturnNothing);
            this.CreateForeignKey(User_DeniedPermissionTable, "UserID", "User", "UserID");
            this.CreateForeignKey(User_DeniedPermissionTable, "DeniedPermissionID", "Permission", "PermissionID");

            Smo.Table Role_PermissionTable = this.GetTable("Role_Permission", TableNotFoundOptionEnum.ReturnNothing);
            this.CreateForeignKey(Role_PermissionTable, "RoleID", "Role", "RoleID");
            this.CreateForeignKey(Role_PermissionTable, "PermissionID", "Permission", "PermissionID");

        }

        /// <summary>
        /// Creates a foreign key in the child table for the identified child column that will reference 
        /// the identified parent column of the parent table.
        /// </summary>
        private void CreateForeignKey(Smo.Table childTable, string childColumnName, string parentTableName, string parentColumnName)
        {
            string FKName = "FK_" + childTable.Name + "_" + parentTableName;
            if (childTable.ForeignKeys.Contains(FKName))
                return;

            Smo.ForeignKey ThisFK = new Smo.ForeignKey(childTable, FKName);
            ThisFK.Columns.Add(new Smo.ForeignKeyColumn(ThisFK, childColumnName, parentColumnName));
            ThisFK.ReferencedTable = parentTableName;
            ThisFK.ReferencedTableSchema = "dbo";
            ThisFK.DeleteAction = Smo.ForeignKeyAction.Cascade;
            ThisFK.UpdateAction = Smo.ForeignKeyAction.Cascade;
            //ThisFK.IsChecked = False
            ThisFK.Create();
        }

        #endregion

        #region CreateTables

        /// <summary>
        /// To create one table for each EntityDef in the Domain.  If a table already 
        /// exists, the existing table will be updated (i.e. new columns added for any
        /// new properties, columns dropped for properties no longer existing). 
        /// </summary>
        public void CreateTables()
        {
            foreach (var ThisED in this.Domain.EntityDefs)
                this.CreateOrUpdateTable(ThisED);
        }

        /// <summary>
        /// To create a new table in this database based on the identified EntityDef.  If
        /// the table already exists, the existing table will be udpated (i.e. new columns added for any
        /// new properties, columns dropped for properties no longer existing). 
        /// </summary>
        public Smo.Table CreateOrUpdateTable(EntityDef thisED)
        {
            // Identify if the table already exists.
            bool TableExisted = this.DoesTableExist(thisED.TableName);

            // Get the table related to this Entity.
            Smo.Table ThisTable = this.GetTable(thisED.TableName, TableNotFoundOptionEnum.CreateTable);

            // Create/update the columns.  (An Entity has three types of properties: scalar 
            // (string, integer, etc.), Entity (person, address, permission, etc.), and 
            // collections of Entities (permissions, addresses, etc.).  Only scalar and entity 
            // properties have columns.
            foreach (PropertyDef ThisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
                this.CreateOrUpdateColumn(ThisPD, ThisTable);

            // All tables get a time-stamp column.
            if (!ThisTable.Columns.Contains("TimeStamp"))
            {
                Smo.Column ThisColumn = new Smo.Column(ThisTable, "TimeStamp");
                ThisTable.Columns.Add(ThisColumn);
                ThisColumn.DataType = Smo.DataType.Timestamp;
                ThisColumn.Nullable = false;
            }

            // If this is an existing table, then there may be columns that need to be deleted.
            if (TableExisted)
            {
                var DeleteTheseColumns = new List<String>();
                foreach (Smo.Column ThisColumn in ThisTable.Columns)
                {
                    if (ThisColumn.Name != "TimeStamp" && !this.DoesPropertyDefintionExist(thisED, ThisColumn.Name))
                    {
                        DeleteTheseColumns.Add(ThisColumn.Name);
                    }
                }
                foreach (string ThisColumnName in DeleteTheseColumns)
                {
                    this.DeleteColumn(ThisColumnName, ThisTable);
                }
            }

            // We are now ready to update/create the table.
            if (TableExisted)
                ThisTable.Alter();
            else
                ThisTable.Create();

            //Smo.ObjectPermissionSet Permissions = new Smo.ObjectPermissionSet();
            //Permissions.Select = true;
            //Permissions.Update = true;
            //Permissions.Insert = true;
            //Permissions.Delete = true;
            //ThisTable.Grant(Permissions, this._database.UserName);

            //If we have a timestamp property defintion, then create the trigger that will keep this 
            //column up to date.
            //If thisED.HasTimeStampPropertyDef Then Me.CreateUpdateTimeStampTrigger(thisED, ThisTable)


            return ThisTable;

        }

        /// <summary>
        /// To determine if a property defintion with the identified field name exists.
        /// </summary>
        private bool DoesPropertyDefintionExist(EntityDef thisED, string ColumnName)
        {
            foreach (var ThisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                if (ThisPD.DatabaseColumnName == ColumnName)
                    return true;
            }
            return false;
        }

        /// <summary>
        /// To get the identified Column.  The ColumnNotFoundOption parameter indicates how this 
        /// procedure should behave if the Column is not found.
        /// </summary>
        private Smo.Column GetColumn(string columnName, Smo.Table thisTable, ColumnNotFoundOptionEnum columnNotFoundOption)
        {
            if (this.DoesColumnExist(columnName, thisTable))
            {
                return thisTable.Columns[columnName];
            }
            else
            {
                switch (columnNotFoundOption)
                {
                    case ColumnNotFoundOptionEnum.ThrowColumnNotFoundException:
                        throw new Exception("Could not find column (" + columnName + ") in table (" + thisTable.Name + ").");
                    case ColumnNotFoundOptionEnum.CreateColumn:
                        return this.CreateNewColumn(columnName, thisTable);
                    default:
                        return null;
                }
            }
        }

        /// <summary>
        /// Identifies options on what should happen if a column is not found in a table.
        /// </summary>
        private enum ColumnNotFoundOptionEnum
        {
            ThrowColumnNotFoundException,
            ReturnNothing,
            CreateColumn
        }

        /// <summary>
        /// To create and return a new Column in the identified table.  If the column already
        /// exists, the ColumnAlreadyExistsException will be thrown.
        /// </summary>
        private Smo.Column CreateNewColumn(string columnName, Smo.Table thisTable)
        {
            if (this.DoesColumnExist(columnName, thisTable))
                throw new Exception("The column '" + columnName + "' already exists in table '" + thisTable.Name + "'.");
            Smo.Column ThisColumn = new Smo.Column(thisTable, columnName);
            thisTable.Columns.Add(ThisColumn);
            return ThisColumn;
        }

        /// <summary>
        /// To create a column in the identified table for the identified property Def.
        /// If a column with same name (as the identified property definiton) already exists, the column
        /// will be updated with any setting changes.
        /// </summary>
        private Smo.Column CreateOrUpdateColumn(PropertyDef thisPD, Smo.Table thisTable)
        {
            var ThisColumn = this.GetColumn(thisPD.DatabaseColumnName, thisTable, ColumnNotFoundOptionEnum.CreateColumn);
            ThisColumn.DataType = thisPD.SmoDataType;
            ThisColumn.Identity = thisPD.SqlIsIdentity;
            if (thisPD.IsPrimaryKey)
                this.AddPrimaryKeyIndex(ThisColumn, thisTable);

            // Under some cicumstances, the column in the database will not allow nulls (e.g. timestamp and identity columns)
            if (thisPD.DataType == "timestamp" || thisPD.SqlIsIdentity)
                ThisColumn.Nullable = false;
            else
                ThisColumn.Nullable = thisPD.IsNullable;


            this.SncyDefaultValueRequirement(thisPD, ThisColumn);
            //this.SyncUniquenessRequirement(thisPD, ThisColumn, thisTable);

            return ThisColumn;
        }

        /// <summary>
        /// To sync the default value requirement for the identified property defintion and its 
        /// respective column.
        /// </summary>
        private void SncyDefaultValueRequirement(PropertyDef thisPD, Smo.Column thisColumn)
        {
            if (thisPD.HasSqlDefaultValue)
            {
                if ((thisColumn.DefaultConstraint == null))
                    thisColumn.AddDefaultConstraint().Text = this.GetFormattedDefaultValue(thisPD);
                else
                    thisColumn.DefaultConstraint.Text = this.GetFormattedDefaultValue(thisPD);
            }
            else
            {
                if (thisColumn.DefaultConstraint != null)
                    thisColumn.DefaultConstraint.Drop();
            }
        }

        /// <summary>
        /// To get the formatted default value from the identified property defintion.  If
        /// the property Def does not have a default value, then an empty string is returned.
        /// The formatted part comes into play when the SQL data type is character based - then default
        /// value then need to be in single quotes.
        /// </summary>
        private string GetFormattedDefaultValue(PropertyDef thisPD)
        {
            if (thisPD.HasSqlDefaultValue)
            {
                //Character-based default values must be delimited by single quotes.
                string DefaultValue = thisPD.SqlDefaultValue;
                switch (thisPD.SqlDataType)
                {
                    case "char":
                    case "varchar":
                    case "text":
                        if (DefaultValue[0] != '\'')
                            DefaultValue = "'" + DefaultValue;
                        if (DefaultValue[DefaultValue.Length - 1] != '\'')
                            DefaultValue += "'";
                        break;
                }

                return DefaultValue;

            }
            else
            {
                return string.Empty;
            }
        }

        /// <summary>
        /// Add the primary key index to the identified column.  If this table already has a primary 
        /// key index, then nothing will be done.
        /// </summary>
        private void AddPrimaryKeyIndex(Smo.Column thisColumn, Smo.Table thisTable)
        {
            //Do not create another primary key index if there already is one.
            if (HasPrimaryKeyIndex(thisTable))
                return;

            var PrimaryKeyIndexName = "PK_" + thisTable.Name;
            if (!thisTable.Indexes.Contains(PrimaryKeyIndexName))
            {
                Smo.Index PrimaryKeyIndex = new Smo.Index(thisTable, PrimaryKeyIndexName);
                thisTable.Indexes.Add(PrimaryKeyIndex);
                PrimaryKeyIndex.IndexedColumns.Add(new Smo.IndexedColumn(PrimaryKeyIndex, thisColumn.Name));
                PrimaryKeyIndex.IsClustered = true;
                PrimaryKeyIndex.IsUnique = true;
                PrimaryKeyIndex.IndexKeyType = Smo.IndexKeyType.DriPrimaryKey;
            }
        }

        /// <summary>
        /// To determine if the identified table has a primary key index.
        /// </summary>
        private bool HasPrimaryKeyIndex(Smo.Table thisTable)
        {
            return GetPrimaryKeyIndex(thisTable) != null;
        }

        /// <summary>
        /// To return the primary key index for the identified table.  If the table does 
        /// not have one, then nothing is returned.
        /// </summary>
        private Smo.Index GetPrimaryKeyIndex(Smo.Table thisTable)
        {
            foreach (Smo.Index ThisIndex in thisTable.Indexes)
            {
                if (ThisIndex.IndexKeyType == Smo.IndexKeyType.DriPrimaryKey)
                    return ThisIndex;
            }
            return null;
        }

        /// <summary>
        /// To delete the identified column from the identified table.  If the column does
        /// not exists, then nothing will happen.
        /// </summary>
        private void DeleteColumn(string columnName, string tableName)
        {
            var ThisTable = this.GetTable(tableName, TableNotFoundOptionEnum.ThrowTableNotFoundException);
            if (this.DoesColumnExist(columnName, ThisTable))
            {
                this.DeleteColumn(columnName, ThisTable);
                ThisTable.Alter();
            }
        }

        /// <summary>
        /// To delete the identified column from the identified table.  If the column does
        /// not exists, then nothing will happen.
        /// </summary>
        private void DeleteColumn(string columnName, Smo.Table thisTable)
        {
            if (this.DoesColumnExist(columnName, thisTable))
            {

                //If this column has a unique index, we must delete that index before we can
                //delete this column.
                string UniqueIndexName = this.GetUniqueIndexName(columnName, thisTable.Name);
                if (thisTable.Indexes.Contains(UniqueIndexName))
                {
                    thisTable.Indexes[UniqueIndexName].Drop();
                }

                thisTable.Columns[columnName].Drop();
            }
        }

        /// <summary>
        /// To determine if the identified table exists in this database.
        /// </summary>
        private bool DoesTableExist(string TableName)
        {
            return this.SqlDatabase.Tables.Contains(TableName);
        }

        /// <summary>
        /// To delete the identified table from this database.  If the table does not exist, then 
        /// nothing will happen.
        /// </summary>
        public void DeleteTable(string TableName)
        {
            if (this.DoesTableExist(TableName))
                this.SqlDatabase.Tables[TableName].Drop();
        }

        /// <summary>
        /// To get the identified table.  The tableNotFoundOption parameter indicates how this procedure 
        /// should behave if the table is not found.
        /// </summary>
        private Smo.Table GetTable(string tableName, TableNotFoundOptionEnum tableNotFoundOption)
        {
            if (this.DoesTableExist(tableName))
            {
                return this.SqlDatabase.Tables[tableName];
            }
            else
            {
                switch (tableNotFoundOption)
                {
                    case TableNotFoundOptionEnum.ThrowTableNotFoundException:
                        throw new Exception("Could not find table (" + tableName + ") in database (" + this.Name + ").");

                    case TableNotFoundOptionEnum.CreateTable:
                        return this.CreateNewTable(tableName);

                    default:
                        return null;
                }
            }
        }

        /// <summary>
        /// Options for what to do when a table is not found.
        /// </summary>
        private enum TableNotFoundOptionEnum
        {
            ThrowTableNotFoundException,
            ReturnNothing,
            CreateTable
        }

        /// <summary>
        /// To create and return a new table in the current database.  If the table already exists, 
        /// an exception will be thrown.
        /// </summary>
        private Smo.Table CreateNewTable(string tableName)
        {
            if (this.DoesTableExist(tableName))
                throw new Exception("The table (" + tableName + ") already exists in database (" + this.Name + ").");
            var ThisTable = new Smo.Table(this.SqlDatabase, tableName);
            return ThisTable;
        }

        /// <summary>
        /// To determine if the identified column exists in the identified table.
        /// </summary>
        private bool DoesColumnExist(string columnName, Smo.Table thisTable)
        {
            return thisTable.Columns.Contains(columnName);
        }

        /// <summary>
        /// 
        /// </summary>
        private string GetUniqueIndexName(string columnName, string tableName)
        {
            return "UniqueIndex_" + tableName + "_" + columnName;
        }

        #endregion

        #region Create Stored Procedures

        #region Insert

        /// <summary>
        /// To create the insert stored procedures, one for each entity Def in the parent solution.  
        /// Any existing stored procedures of the same name will be overwritten.
        /// </summary>
        private void CreateInsertStoredPrcoedures()
        {
            foreach (var ThisED in this.Domain.EntityDefs)
                this.CreateInsertStoredPrcoedure(ThisED);
        }

        /// <summary>
        /// To create the insert stored procedure for the identified entity Def.  Any 
        /// existing stored procedures of the same name will be overwritten.
        /// </summary>
        public void CreateInsertStoredPrcoedure(EntityDef thisED)
        {
            var ThisSP = GetInsertStoredProcedure(thisED);
            this.DeleteStoredProcedure(ThisSP.Name);
            ThisSP.Create();
        }

        /// <summary>
        /// Gets an SMO stored procedure for inserting a new record into the table for the identified EntityDef.
        /// </summary>
        private Smo.StoredProcedure GetInsertStoredProcedure(EntityDef thisED)
        {
            var ThisSP = new Smo.StoredProcedure(this.SqlDatabase, thisED.StoredProcedureNames.Insert());
            ThisSP.TextMode = false;

            //Add the parameters.
            this.AddUserCredentialParameters(ThisSP);
            this.AddTimeStampParameter(ThisSP);
            foreach (PropertyDef thisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                var ThisParameter = new Smo.StoredProcedureParameter(ThisSP, "@" + thisPD.DatabaseColumnName, thisPD.SmoDataType);
                ThisParameter.DefaultValue = "NULL";
                ThisSP.Parameters.Add(ThisParameter);
            }

            //Identify the columns.
            string Columns = " (";
            foreach (PropertyDef thisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                Columns += "[" + thisPD.DatabaseColumnName + "], ";
            }
            Columns = Columns.Remove(Columns.Length - 2, 2) + ") ";

            //Identify the values.
            var Values = "VALUES (";
            foreach (PropertyDef thisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                Values += "@" + thisPD.DatabaseColumnName + ", ";
            }
            Values = Values.Remove(Values.Length - 2, 2) + ") ";

            //Create the body of this stored procedure.
            var TableName = "[" + thisED.TableName + "]";
            var Body = new SqlStringBuilder();
            this.AddVerifyUsersCredentials(Body);
            this.AddSecurity(Body, "Create", thisED);
            Body.AddLine("INSERT INTO " + TableName + Columns);
            Body.AddLine(Values);
            Body.AddLine("SELECT @TimeStamp = " + TableName + ".[TimeStamp] FROM " + TableName + " WHERE " + thisED.PrimaryKeyPropertyDef.DatabaseColumnName + " = @" + thisED.PrimaryKeyPropertyDef.DatabaseColumnName);

            ThisSP.TextBody = Body.ToString();
            return ThisSP;
        }

        #endregion

        #region Delete

        /// <summary>
        /// To create the standard 'Delete' stored procedures, one for each root entity in our solution.  
        /// Any existing stored procedures of the same name will NOT be overwritten.  
        /// </summary>
        private void CreateDeleteStoredProcedures()
        {
            foreach (var ThisED in this.Domain.EntityDefs)
                this.CreateDeleteStoredProcedure(ThisED);
        }

        /// <summary>
        /// To create the Delete stored procedure for the identified entity Def.  If the stored procedure 
        /// already exists, it will not be overwritten.
        /// </summary>
        private void CreateDeleteStoredProcedure(EntityDef thisED)
        {
            //Do not overwrite stored procedure if it already exists.
            if (this.DoesStoredProcedureExist(thisED.StoredProcedureNames.Delete())) return;

            var ThisSP = GetDeleteStoredProcedure(thisED);
            this.DeleteStoredProcedure(ThisSP.Name);
            ThisSP.Create();
        }

        /// <summary>
        /// Gets a stored procedure for Deleteing a record from the table for our entity Def.
        /// </summary>
        private Smo.StoredProcedure GetDeleteStoredProcedure(EntityDef thisED)
        {

            var ThisSP = new Smo.StoredProcedure(this.SqlDatabase, thisED.StoredProcedureNames.Delete());
            ThisSP.TextMode = false;

            //Add the parameters.
            this.AddUserCredentialParameters(ThisSP);
            var IDParameter = new Smo.StoredProcedureParameter(ThisSP, "@" + thisED.PrimaryKeyPropertyDef.DatabaseColumnName, Smo.DataType.UniqueIdentifier);
            ThisSP.Parameters.Add(IDParameter);

            //Create the body of this stored procedure.
            var Body = new SqlStringBuilder();
            this.AddVerifyUsersCredentials(Body);
            this.AddSecurity(Body, "Delete", thisED);
            Body.AddLine("DELETE FROM [" + thisED.Name + "]");
            Body.AddLine("WHERE " + thisED.PrimaryKeyPropertyDef.DatabaseColumnName + " = @" + thisED.PrimaryKeyPropertyDef.DatabaseColumnName);

            ThisSP.TextBody = Body.ToString();
            return ThisSP;
        }

        #endregion

        #region Update

        /// <summary>
        /// To create the standard 'Update' stored procedures, one for each entity in our solution.
        /// Any existing stored procedures of the same name will NOT be overwritten.  
        /// </summary>
        private void CreateUpdateStoredProcedures()
        {
            foreach (var ThisED in this.Domain.EntityDefs)
                this.CreateUpdateStoredProcedure(ThisED);
        }

        /// <summary>
        /// To create the Update stored procedure for the identified entity Def.  Any existing stored 
        /// procedures of the same name will be overwritten.
        /// </summary>
        public void CreateUpdateStoredProcedure(EntityDef thisED)
        {
            Smo.StoredProcedure ThisSP = this.GetUpdateStoredProcedure(thisED);
            this.DeleteStoredProcedure(ThisSP.Name);
            ThisSP.Create();
        }

        /// <summary>
        /// Gets a stored procedure for Updateing a new record into the table for our entity Def.
        /// </summary>
        /// <param name="targetDatabase"></param>
        /// <returns></returns>
        private Smo.StoredProcedure GetUpdateStoredProcedure(EntityDef thisED)
        {
            var ThisSP = new Smo.StoredProcedure(this.SqlDatabase, thisED.StoredProcedureNames.Update());
            ThisSP.TextMode = false;

            //Add the parameters.
            this.AddUserCredentialParameters(ThisSP);
            AddTimeStampParameter(ThisSP);
            foreach (PropertyDef thisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                var ThisParameter = new Smo.StoredProcedureParameter(ThisSP, "@" + thisPD.DatabaseColumnName, thisPD.SmoDataType);
                ThisParameter.DefaultValue = "NULL";
                ThisSP.Parameters.Add(ThisParameter);
            }

            //Identify the set values.
            var SetValues = "SET ";
            foreach (var thisPD in thisED.PropertyDefs.ScalarOrEntityProperties)
            {
                SetValues += "[" + thisPD.DatabaseColumnName + "] = " + "@" + thisPD.DatabaseColumnName + ", ";
            }
            SetValues = SetValues.Remove(SetValues.Length - 2, 2);

            //Create the body of this stored procedure.
            var TableName = "[" + thisED.TableName + "]";
            var Body = new SqlStringBuilder();
            this.AddVerifyUsersCredentials(Body);
            this.AddSecurity(Body, "Update", thisED);
            AddConcurrencyCheck(thisED, Body);
            Body.AddLine("UPDATE " + TableName);
            Body.AddLine(SetValues);
            Body.AddLine("WHERE " + thisED.PrimaryKeyPropertyDef.DatabaseColumnName + " = @" + thisED.PrimaryKeyPropertyDef.DatabaseColumnName + " AND [TimeStamp] = @TimeStamp");
            Body.AddLine("SELECT @TimeStamp = " + TableName + ".[TimeStamp] FROM " + TableName + " WHERE " + thisED.PrimaryKeyPropertyDef.DatabaseColumnName + " = @" + thisED.PrimaryKeyPropertyDef.DatabaseColumnName);
            ThisSP.TextBody = Body.ToString();
            return ThisSP;
        }

        /// <summary>
        /// Adds code to the body that verifies the row exists and it has not changed since it was last read.
        /// </summary>
        private void AddConcurrencyCheck(EntityDef thisED, SqlStringBuilder body)
        {
            string TableName = "[" + thisED.TableName + "]";
            string PK = thisED.PrimaryKeyPropertyDef.DatabaseColumnName;
            string SPName = thisED.StoredProcedureNames.Update();
            var lines = body;
            lines.AddCommentBlock("VERIFY ROW EXISTS");
            lines.AddLine("DECLARE @RowCount int");
            lines.AddLine("SELECT @RowCount = Count(*) FROM " + TableName + " WHERE " + PK + " = @" + PK);
            lines.AddLine("IF @RowCount = 0");
            lines.AddLine("BEGIN");
            lines.AddLine("\tDECLARE @MSG varchar(200) = 'Row not found: " + SPName + " - ' + CAST(@" + PK + " as varchar(36))");
            lines.AddLine("\tRAISERROR (@MSG, 11, 1)");
            lines.AddLine("\tRETURN");
            lines.AddLine("END");
            lines.AddBlankLine();

            lines.AddCommentBlock("VERIFY ROW HAS NOT BEEN UPDATED SINCE IT WAS ACCESSED BY THIS USER");
            lines.AddLine("SELECT @RowCount = Count(*) FROM " + TableName + " WHERE " + PK + " = @" + PK + " AND [TimeStamp] = @TimeStamp");
            lines.AddLine("IF @RowCount = 0");
            lines.AddLine("BEGIN");
            lines.AddLine("\tSET @MSG = 'Concurrency Violation: Row has been edited: " + SPName + " - ' + CAST(@" + PK + " as varchar(36))");
            lines.AddLine("\tRAISERROR (@MSG, 11, 1)");
            lines.AddLine("\tRETURN");
            lines.AddLine("END");
            lines.AddBlankLine();

        }

        #endregion

        #region Get

        /// <summary>
        /// To create the standard 'Get' stored procedures, one for each aggregate that exists in
        /// our domain.  Any existing stored procedures of the same name will be overwritten, except
        /// if the aggregate's CreateStoredProcedure property is set to False.
        /// </summary>
        private void CreateGetStoredProcedures()
        {
            foreach (var ThisED in this.Domain.EntityDefs)
                foreach (var ThisAD in ThisED.AggregateDefs)
                    this.CreateGetStoredPrcoedure(ThisAD);
        }

        /// <summary>
        /// To create the get stored procedure for this entity Def for the identified aggregate 
        /// Def.  Any existing stored procedure of the same name will be overwritten.
        /// </summary>
        public void CreateGetStoredPrcoedure(AggregateDef thisAD)
        {
            //Never create/modifiy a stored procedure for a view that has been marked as such.
            if (!thisAD.CreateStoredProcedure) return;

            var ThisSP = this.GetGetStoredProcedure(thisAD);
            this.DeleteStoredProcedure(ThisSP.Name);
            ThisSP.Create();
        }

        /// <summary>
        /// Creates the stored procedure for getting the data required by the identified AggregateDef
        /// </summary>
        private Smo.StoredProcedure GetGetStoredProcedure(AggregateDef thisAD)
        {
            var ThisSP = new Smo.StoredProcedure(this.SqlDatabase, thisAD.StoredProcedureName);
            ThisSP.TextMode = false;

            //Add the parameters.
            this.AddUserCredentialParameters(ThisSP);
            Smo.StoredProcedureParameter FilterParameter = new Smo.StoredProcedureParameter(ThisSP, "@Filter", Smo.DataType.VarCharMax);
            FilterParameter.DefaultValue = "'SELECT " + thisAD.EntityDef.PrimaryKeyPropertyDef.DatabaseColumnName + " FROM [" + thisAD.EntityDef.TableName + "]'";
            ThisSP.Parameters.Add(FilterParameter);

            //Create the body of the stored procedure.
            var Body = new SqlStringBuilder();

            this.AddVerifyUsersCredentials(Body);
            //Verfiy that the user has permission to read all the data.
            Body.AddCommentBlock("VERIFY THAT THE USER HAS PERMISSION TO READ ALL THE DATA");
            this.AddSecurity(Body, "Read", thisAD.EntityDef.Name);
            foreach (var ThisED in thisAD.GetUniqueEntities().OrderBy(i => i.Name))
                this.AddSecurity(Body, "Read", ThisED.Name);

            CreateStoredProcedureTemporaryTables(thisAD, Body);
            CreateStoredProcedureSelectStatements(thisAD, Body);
            ThisSP.TextBody = Body.ToString();

            return ThisSP;
        }

        /// <summary>
        /// 
        /// </summary>
        private void CreateStoredProcedureTemporaryTables(AggregateDef thisAD, SqlStringBuilder lines)
        {
            lines.AddCommentBlock("CREATE TEMPORARY TABLES THAT UNIQUELY IDENTIFY THE DATA RELATED TO THE IDENTIFIED FILTER");

            string TableName = null;
            string TempTableName = null;

            //Add the root table.  This must always be the first select.
            var ThisEntity = thisAD.EntityDef;
            TableName = ThisEntity.TableName;
            TempTableName = "#Root" + TableName + "IDs";
            lines.AddComment(TableName);
            lines.AddLine("CREATE TABLE " + TempTableName + " (" + ThisEntity.PrimaryKeyPropertyDef.DatabaseColumnName + " uniqueidentifier)");
            lines.AddLine("INSERT INTO " + TempTableName + " EXEC(@Filter)");
            lines.AddBlankLine();

            foreach (var ThisED in thisAD.GetUniqueEntities())
            {
                TableName = ThisED.TableName;
                TempTableName = "#" + TableName + "IDs";
                lines.AddComment(TableName);
                lines.AddLine("CREATE TABLE " + TempTableName + " (" + ThisED.PrimaryKeyPropertyDef.DatabaseColumnName + " uniqueidentifier)");
                lines.AddLine("INSERT INTO " + TempTableName);
                AddTemporaryTableSelectStatements(thisAD, ThisED, lines);
                lines.AddBlankLine();
            }

            lines.AddBlankLine();
        }

        /// <summary>
        /// To add the SQL select statements that will uniquely identify the ids for the identified entity Def.
        /// </summary>
        private void AddTemporaryTableSelectStatements(AggregateDef thisAD, EntityDef thisED, SqlStringBuilder lines)
        {
            bool SelectAlreadyAdded = false;
            foreach (var ThisDE in thisAD.GetRelatedDataElements(thisED))
            {
                if (SelectAlreadyAdded)
                    lines.AddLine("  UNION");
                AddSelectDistinctStatement(thisAD, ThisDE, lines);
                AddTemporaryTableFromClause(thisAD, ThisDE, lines);
                SelectAlreadyAdded = true;
            }

        }

        /// <summary>
        /// 
        /// </summary>
        private void AddSelectDistinctStatement(AggregateDef thisAD, DataElement thisDE, SqlStringBuilder lines)
        {
            var RootDE = thisAD.DataElement;
            var ParentDE = thisDE.ParentDataElement;

            if (ParentDE == RootDE)
            {
                if (thisDE.IsCollectionType)
                {
                    lines.AddLine("    SELECT DISTINCT [" + thisDE.PropertyDef.DataTypeEntityDef.Name + "]." + thisDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName);
                }
                else
                {
                    lines.AddLine("    SELECT DISTINCT [" + thisAD.EntityDef.TableName + "]." + thisDE.PropertyDef.DatabaseColumnName);
                }

            }
            else
            {
                if (thisDE.IsCollectionType)
                {
                    lines.AddLine("    SELECT DISTINCT [" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]." + thisDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName);
                }
                else
                {
                    lines.AddLine("    SELECT DISTINCT [" + ParentDE.PropertyDef.DataTypeEntityDef.TableName + "]." + thisDE.PropertyDef.DatabaseColumnName);
                }

            }


        }

        /// <summary>
        /// To add the SQL FROM clause for the identified data element.
        /// </summary>
        private void AddTemporaryTableFromClause(AggregateDef thisAD, DataElement thisDE, SqlStringBuilder lines)
        {
            DataElement RootDE = thisAD.DataElement;

            //First, add the FROM clause.
            if (thisDE.IsCollectionType)
            {
                lines.AddLine("    FROM [" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]");
            }
            else if (thisDE.ParentDataElement == RootDE)
            {
                lines.AddLine("    FROM [" + thisAD.EntityDef.TableName + "]");
            }
            else
            {
                lines.AddLine("    FROM [" + thisDE.ParentDataElement.PropertyDef.DataTypeEntityDef.TableName + "]");
            }

            //Next, add the JOIN clauses.  To accomplish this, we must loop up the relationship stack,
            //add a JOIN clause for each relationship, and stop when we get to the root data element.
            //If our first data element is not a collection type, then we do not need the first
            //JOIN statement.
            var CurrentDE = thisDE;
            if (!CurrentDE.IsCollectionType)
                CurrentDE = CurrentDE.ParentDataElement;
            if (CurrentDE != RootDE)
            {
                do
                {
                    AddTemporaryTableJoinClause(thisAD, RootDE, CurrentDE, lines);
                    CurrentDE = CurrentDE.ParentDataElement;
                } while (!(CurrentDE == RootDE));
            }

            //Add the last join, which is always to our root table.
            AddRootJoin(thisAD, lines);

        }

        /// <summary>
        /// 
        /// </summary>
        private void AddTemporaryTableJoinClause(AggregateDef thisAD, DataElement rootDE, DataElement thisDE, SqlStringBuilder lines)
        {
            string TableName = null;
            string KeyField = null;
            string JoinToTableName = null;
            string JoinToKeyField = null;

            //Identify the next data element up the relationship chain.
            var NextDE = thisDE.ParentDataElement;

            //In this procedure, we have three related data elements (from top to bottom of the 
            //relationship stack):
            //   * NextDE
            //   * ThisDE
            //   * PreviousDE
            //
            //We use the relationship data from these three data elements to create the JOIN clause.

            //When the next data element is our root data element, we are forced to handle this situation
            //specially due to the fact that the root data element does not provide us with the necessary
            //data - we must go to the view to get that data.
            if (NextDE == rootDE)
            {
                if (thisDE.IsCollectionType)
                {
                    TableName = "[" + thisAD.EntityDef.TableName + "]";
                    JoinToTableName = "[" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    KeyField = thisAD.EntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;
                    JoinToKeyField = thisDE.PropertyDef.ForeignKeyPropertyDef.DatabaseColumnName;

                }
                else
                {
                    TableName = "[" + thisAD.EntityDef.TableName + "]";
                    JoinToTableName = "[" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    KeyField = thisDE.PropertyDef.DatabaseColumnName;
                    JoinToKeyField = thisDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;
                }

            }
            else
            {
                if (thisDE.IsCollectionType)
                {
                    TableName = "[" + NextDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    JoinToTableName = "[" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    KeyField = NextDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;
                    JoinToKeyField = thisDE.PropertyDef.ForeignKeyPropertyDef.DatabaseColumnName;

                }
                else if (NextDE.IsCollectionType)
                {
                    TableName = "[" + NextDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    JoinToTableName = "[" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    KeyField = thisDE.PropertyDef.DatabaseColumnName;
                    JoinToKeyField = thisDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;

                }
                else
                {
                    TableName = "[" + NextDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    JoinToTableName = "[" + thisDE.PropertyDef.DataTypeEntityDef.TableName + "]";
                    KeyField = thisDE.PropertyDef.DatabaseColumnName;
                    JoinToKeyField = thisDE.PropertyDef.DataTypeEntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;
                }

            }

            //We can now create and add the JOIN clause.
            lines.AddLine("    JOIN " + TableName + " ON " + TableName + "." + KeyField + " = " + JoinToTableName + "." + JoinToKeyField);

        }

        /// <summary>
        /// Adds the JOIN statement to the root table of IDs for the identified view.
        /// </summary>
        private void AddRootJoin(AggregateDef thisAD, SqlStringBuilder lines)
        {
            string RootTableName = "#Root" + thisAD.EntityDef.TableName + "IDs";
            string KeyField = thisAD.EntityDef.PrimaryKeyPropertyDef.DatabaseColumnName;
            string JoinToTableName = "[" + thisAD.EntityDef.TableName + "]";
            lines.AddLine("    JOIN " + RootTableName + " ON " + RootTableName + "." + KeyField + " = " + JoinToTableName + "." + KeyField);
        }

        /// <summary>
        /// Creates the Select statements for the identified AggregateDef and adds them to the identified lines.
        /// </summary>
        private void CreateStoredProcedureSelectStatements(AggregateDef thisAD, SqlStringBuilder lines)
        {
            lines.AddBlankLine();
            lines.AddComment("------------------------------------------------------------------------------------------");
            lines.AddComment("BEGIN RETURNING DATA");
            lines.AddComment("------------------------------------------------------------------------------------------");

            //Add the root table.  This must always be the first select.
            var TableName = thisAD.EntityDef.TableName;
            lines.AddComment("Root" + TableName);
            if (thisAD.DataElement.IncludeAllScalarTypeProperties)
                lines.AddLine("SELECT [" + TableName + "].* ");
            else
                AddSelectFieldList(thisAD, thisAD.EntityDef, TableName, lines);

            AddFromAndJoinLine(TableName, thisAD.EntityDef.PrimaryKeyPropertyDef.DatabaseColumnName, true, lines);

            //Add all child tables.
            foreach (var ThisED in thisAD.GetUniqueEntities().OrderBy(i => i.Name))
                AddSelectStatement(thisAD, ThisED, lines);

            lines.AddBlankLine();
        }

        /// <summary>
        /// To add the SQL select statement for the identified entity Def.
        /// </summary>
        private void AddSelectStatement(AggregateDef thisAD, EntityDef thisED, SqlStringBuilder lines)
        {
            var TableName = thisED.TableName;
            lines.AddComment(TableName);
            if (thisAD.IncludeAllScalarTypeProperties(thisED))
                lines.AddLine("SELECT [" + TableName + "].* ");
            else
                AddSelectFieldList(thisAD, thisED, TableName, lines);

            AddFromAndJoinLine(TableName, thisED.PrimaryKeyPropertyDef.DatabaseColumnName, false, lines);
        }

        /// <summary>
        /// To add the SQL select field list for the identified entity Def.
        /// </summary>
        private void AddSelectFieldList(AggregateDef thisAD, EntityDef thisED, string tableName, SqlStringBuilder lines)
        {
            lines.Add("SELECT ");
            bool FieldAlreadyAdded = false;
            foreach (string ThisFieldName in GetDistinctFieldNames(thisAD, thisED))
            {
                if (FieldAlreadyAdded) lines.Add(", ");
                lines.Add("[" + tableName + "]." + ThisFieldName);
                FieldAlreadyAdded = true;
            }
            lines.AddBlankLine();
        }

        /// <summary>
        /// Gets the distinct field names that should be included.
        /// </summary>
        private StringCollection GetDistinctFieldNames(AggregateDef thisAD, EntityDef thisED)
        {
            var FieldNames = new StringCollection();

            //Always include the primary key.
            FieldNames.Add(thisED.PrimaryKeyPropertyDef.DatabaseColumnName);

            //Next, add fields that are referenced in all value/entity data elements in our view Def.
            PropertyDef ThisPD;
            foreach (var ThisDE in thisAD.DataElement.DataElements.GetAll())
            {
                ThisPD = ThisDE.PropertyDef;
                if (ThisPD.ParentEntity == thisED && ThisPD.IsScalarOrEntityType)
                {
                    if (!FieldNames.Contains(ThisPD.DatabaseColumnName))
                        FieldNames.Add(ThisPD.DatabaseColumnName);
                }
            }

            // For a collection type data element, the child element usually points back to the
            // parent of the collection.  However, in the current UI of creating a View Def,
            // the user is not allowed to click on the child data element to link back to the parent 
            // (since this currently will result in recreating the parent entity).  However, we
            // must have the parent id to link back to the parent.  For now, we will bring back
            // all foreign keys.
            foreach (var ChildPD in thisED.PropertyDefs.EntityProperties)
            {
                if (!FieldNames.Contains(ChildPD.DatabaseColumnName))
                    FieldNames.Add(ChildPD.DatabaseColumnName);
            }

            return FieldNames;
        }

        /// <summary>
        /// To add the SQL from and join line for the Returning Data select statement.
        /// </summary>
        private void AddFromAndJoinLine(string tableName, string keyColumnName, bool isRoot, SqlStringBuilder lines)
        {
            var TempTableName = (isRoot ? "#Root" : "#") + tableName + "IDs";
            lines.AddLine("FROM [" + tableName + "] JOIN " + TempTableName + " ON [" + tableName + "]." + keyColumnName + " = " + TempTableName + "." + keyColumnName);
            lines.AddBlankLine();
        }

        #endregion

        #region AddTimeStampParameter

        /// <summary>
        /// Adds the time stamp parameter to the identified stored procedure.
        /// </summary>
        private void AddTimeStampParameter(Smo.StoredProcedure thisSP)
        {
            Smo.StoredProcedureParameter ThisParameter = new Smo.StoredProcedureParameter(thisSP, "@TimeStamp", Smo.DataType.Timestamp);
            ThisParameter.IsOutputParameter = true;
            thisSP.Parameters.Add(ThisParameter);
        }

        #endregion

        #region DoesStoredProcedureExist

        /// <summary>
        /// To determine if the identified stored procedure exists in this database.
        /// </summary>
        private bool DoesStoredProcedureExist(string storedProcedureName)
        {
            return this.SqlDatabase.StoredProcedures.Contains(storedProcedureName);
        }

        #endregion

        #region DeleteStoredProcedure

        /// <summary>
        /// To delete the identified stored procedure from this database.  If the stored  procedure does 
        /// not exist, then nothing will happen.
        /// </summary>
        private void DeleteStoredProcedure(string storedProcedureName)
        {
            if (this.DoesStoredProcedureExist(storedProcedureName))
                this.SqlDatabase.StoredProcedures[storedProcedureName].Drop();
        }

        #endregion

        #region AddSecurity

        /// <summary>
        /// To add the security statements to the identified body.
        /// </summary>
        private void AddSecurity(SqlStringBuilder body, string operationType, EntityDef thisED)
        {
            this.AddSecurity(body, operationType, thisED.Name);
        }

        private void AddSecurity(SqlStringBuilder body, string operationType, string objectName)
        {
            body.AddLine("IF dbo.CanExecute(@ThisUserID, '" + objectName + "', '" + operationType + "') = 0 ");
            body.AddLine("BEGIN RAISERROR ('Authorization Failed: " + operationType + " " + objectName + "', 11, 1) RETURN END");
            body.AddBlankLine();
        }

        #endregion

        #region AddVerifyUsersCredentials

        /// <summary>
        /// To add the statements that will verify the user credentials.
        /// </summary>
        private void AddVerifyUsersCredentials(SqlStringBuilder body)
        {
            body.AddCommentBlock("VERIFY USER CREDENTIALS");
            body.AddLine("DECLARE @ThisUserID uniqueidentifier");
            body.AddLine("SET @ThisUserID = dbo.GetUserID(@ThisUser, @UserPassword)");
            body.AddLine("IF @ThisUserID IS NULL BEGIN RAISERROR ('Invalid User Account', 11, 1) RETURN END");
            body.AddBlankLine();
        }

        #endregion

        #endregion

        #region AddUserCredentialParameters

        /// <summary>
        /// Adds the UserName and Password parameters to the identified stored procedure.
        /// </summary>
        private void AddUserCredentialParameters(Smo.StoredProcedure thisSP)
        {

            //User Name.
            var UserNameParameter = new Smo.StoredProcedureParameter(thisSP, "@ThisUser", this.GetUserNameDataType());
            thisSP.Parameters.Add(UserNameParameter);

            //Password.
            var PasswordParameter = new Smo.StoredProcedureParameter(thisSP, "@UserPassword", this.GetPasswordDataType());
            thisSP.Parameters.Add(PasswordParameter);

        }


        private Smo.DataType GetUserNameDataType()
        {
            var UserED = this.Domain.EntityDefs.Find("User");
            return UserED.PropertyDefs.Find("UserName").SmoDataType;
        }

        private Smo.DataType GetPasswordDataType()
        {
            var UserED = this.Domain.EntityDefs.Find("User");
            return UserED.PropertyDefs.Find("Password").SmoDataType;
        }

        #endregion

        #region FriendlyID

        private const string FRIENDLY_ID_TABLE_NAME = "FriendlyID";

        /// <summary>
        /// To create the special FriendlyID table, which is used to support creating friendly IDs.  
        /// If this table already exists, then nothing will be done.
        /// </summary>
        public void CreateFriendlyIDTable()
        {
            // Nothing to do if table already exists.
            if (this.SqlDatabase.Tables.Contains(FRIENDLY_ID_TABLE_NAME)) return;

            // Create the table.
            var FriendlyIDTable = new Smo.Table(this.SqlDatabase, FRIENDLY_ID_TABLE_NAME);

            // Add the columns
            var EntityNameColumn = new Smo.Column(FriendlyIDTable, "EntityName");
            FriendlyIDTable.Columns.Add(EntityNameColumn);
            EntityNameColumn.DataType = Smo.DataType.VarChar(100);

            var NextFriendlyIDColumn = new Smo.Column(FriendlyIDTable, "NextFriendlyID");
            FriendlyIDTable.Columns.Add(NextFriendlyIDColumn);
            NextFriendlyIDColumn.DataType = Smo.DataType.Int;

            FriendlyIDTable.Create();

            // Give permission to the User account to access this table.
            //var Permissions = new Smo.ObjectPermissionSet();
            //Permissions.Select = true;
            //Permissions.Update = true;
            //Permissions.Insert = true;
            //Permissions.Delete = true;
            //FriendlyIDTable.Grant(Permissions, this._database.UserName);
        }

        /// <summary>
        /// To add one row to the FriendlyID table for each EnittyDefintion that has a FriendlyID 
        /// property defintion.  If the FriendlyID table does not exist, it will be created.
        /// </summary>
        private void AddFriendlyIdRows()
        {

            //Make sure the table exists.
            this.CreateFriendlyIDTable();

            using (var ThisConnection = this.GetOpenConnection())
            {
                foreach (var ThisED in this.Domain.EntityDefs)
                    if (ThisED.HasFriendlyIDPropertyDef)
                        this.AddFriendlyIdRow(ThisED, ThisConnection);

                ThisConnection.Close();
            }
        }

        /// <summary>
        /// To add a row to the FriendlyID table for the identified entity defintion.
        /// </summary>
        public void AddFriendlyIdRow(EntityDef thisED)
        {
            this.AddFriendlyIdRow(thisED, this.GetOpenConnection());
        }

        /// <summary>
        /// To add a row to the FriendlyID table for the identified entity defintion.
        /// </summary>
        private void AddFriendlyIdRow(EntityDef thisED, SqlConnection thisConnection)
        {
            this.ExecuteTextCommand("IF (SELECT Count(*) FROM FriendlyID WHERE EntityName = '" + thisED.Name + "') = 0 INSERT INTO FriendlyID VALUES('" + thisED.Name + "', 1)", thisConnection);
        }

        /// <summary>
        /// Removes the FriendlyID row for the identified EntityDef.
        /// </summary>
        public void RemoveFriendlyIdRow(EntityDef thisED)
        {
            using (var ThisConnection = this.GetOpenConnection())
            {
                this.ExecuteTextCommand("DELETE FROM FriendlyID WHERE EntityName = '" + thisED.Name + "'", ThisConnection);
                ThisConnection.Close();
            }
        }


        /// <summary>
        /// To create the special GetNextFriendlyID stored procedure.
        /// </summary>
        private void CreateGetNextFriendlyIdStoredProcedure()
        {
            //Remove any possible existing stored procedure of the same name.
            this.DeleteStoredProcedure("GetNextFriendlyID");

            Smo.StoredProcedure ThisSP = new Smo.StoredProcedure(this.SqlDatabase, "GetNextFriendlyID");
            ThisSP.TextMode = false;

            //Add the EntityName parameter.
            Smo.StoredProcedureParameter EntityNameParameter = new Smo.StoredProcedureParameter(ThisSP, "@EntityName", Smo.DataType.VarChar(100));
            ThisSP.Parameters.Add(EntityNameParameter);

            //Add the NextFriendlyID parameter.
            Smo.StoredProcedureParameter NextFriendlyIDParameter = new Smo.StoredProcedureParameter(ThisSP, "@NextFriendlyID", Smo.DataType.Int);
            NextFriendlyIDParameter.IsOutputParameter = true;
            ThisSP.Parameters.Add(NextFriendlyIDParameter);

            //Create the body of the stored procedure.
            ThisSP.TextBody = "SELECT @NextFriendlyID = NextFriendlyID FROM FriendlyID WHERE EntityName = @EntityName " + Environment.NewLine +
                              "UPDATE FriendlyID SET NextFriendlyID = @NextFriendlyID + 1 WHERE EntityName = @EntityName";
            ThisSP.Create();
        }


        #endregion

        #region GetOpenConnection

        /// <summary>
        /// To create and open a connection to the database.
        /// </summary>
        private SqlConnection GetOpenConnection()
        {
            //string ConnectionString = "data source=" + this.Server + ";initial catalog=" + this.Name + ";Integrated Security=True";
            string ConnectionString = "data source=" + this.Server + ";initial catalog=" + this.Name + ";Integrated Security=False;User ID=Adam;Password=ass";
            //Data Source=aiserver3;Integrated Security=False;User ID=Adam;Password=********;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False
            SqlConnection ThisConnection = new SqlConnection(ConnectionString);
            ThisConnection.Open();
            return ThisConnection;
        }


        #endregion

        #region Server Support

        /// <summary>
        /// To get the server on the current network with the identified server name.  If not
        /// found or is not available, an exception is thrown.
        /// </summary>
        private Smo.Server GetServer(string serverName)
        {
            //string ConnectionString = "data source=" + this.Server + ";initial catalog=" + this.Name + ";Integrated Security=False;User ID=Adam;Password=ass";
            string ConnectionString = "data source=" + this.Server + ";Integrated Security=False;User ID=Adam;Password=ass";
            //string ConnectionString = "data source=" + this.Server + ";initial catalog=" + this.Name + ";Integrated Security=True;";

            CheckServerAvailability(serverName);
            var serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection();
            serverConnection.ConnectionString = ConnectionString;
            return new Smo.Server(serverConnection);
            //return new Smo.Server(ConnectionString);
        }

        /// <summary>
        /// To check if the identifies server is currently available on the network. If it is, 
        /// then nothing happens; if not, an exception will be thrown.
        /// </summary>
        private void CheckServerAvailability(string serverName)
        {
            if (!IsServerAvailable(serverName))
                throw new Exception("Server '" + serverName + "' is not available at this time.");
        }

        /// <summary>
        /// To determine if the identified server is currently available on the network.
        /// </summary>
        private bool IsServerAvailable(string serverName)
        {
            try
            {
                Smo.Server ThisServer = new Smo.Server(serverName);
                ThisServer.PingSqlServerVersion(serverName);
                return true;
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
                return false;
            }
        }

        #endregion

        #region ExecuteTextCommand

        /// <summary>
        /// Executes the command as identified in the text.
        /// </summary>
        private void ExecuteTextCommand(string text, SqlConnection thisConnection)
        {
            var ThisCommand = new SqlCommand();
            ThisCommand.CommandType = System.Data.CommandType.Text;
            ThisCommand.CommandText = text;
            ThisCommand.Connection = thisConnection;
            ThisCommand.ExecuteNonQuery();
        }


        #endregion
    }
}

